package com.ytzl.itrip;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by sam on 2018/2/9.
 */
public class TableHandler {
    //数据库连接信息
    public static final String DRIVERCLASS = "com.mysql.jdbc.Driver";
    public static final String JDBCURL = "jdbc:mysql://localhost:3306/itripdb";
    public static final String USERNAME = "samson";
    public static final String PASSWORD = "samson";

    /**
     * 获取数据库连接
     *
     * @return
     */
    public Connection getConnection() {
        Connection connection = null;
        try {
            Class.forName(DRIVERCLASS);
            //获取数据库连接
            connection = DriverManager.getConnection(JDBCURL, USERNAME, PASSWORD);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void main(String[] args) {
        TableHandler tableHandler = new TableHandler();
        List<Table> tables = tableHandler.getTables();
        System.out.print(tables.size());
    }

    /**
     * 获取数据库中所有表信息
     *
     * @return
     */
    public List<Table> getTables() {
        List<Table> tables = new ArrayList<>();
        try {
            Connection connection = getConnection();
            DatabaseMetaData dbmd = connection.getMetaData();
            ResultSet resultSet = dbmd.getTables(null, null, null, new String[]{"TABLE"});
            while (resultSet.next()) {
                String table_name = resultSet.getString("TABLE_NAME");
                Table table = new Table();
                table.setTableName(table_name);
                //构建类的名称 table_name --> tableName
                String[] split = table_name.split("_");
                String className = split[0].substring(0, 1).toUpperCase() + split[0].substring(1);
                String lowerClassName = split[0];
                for (int i = 1; i < split.length; i++) {
                    lowerClassName += split[i].substring(0, 1).toUpperCase() + split[i].substring(1);
                    className += split[i].substring(0, 1).toUpperCase() + split[i].substring(1);
                }
                table.setClassName(className);
                table.setLowerClassName(lowerClassName);
                //获取表的描述
                table.setComment(getTableComment(table_name));
                //添加列
                table.setColumns(getColumns(table_name));
                tables.add(table);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return tables;
    }

    /**
     * 通过表名获取列
     *
     * @param table_name
     * @return
     */
    private List<Column> getColumns(String table_name) {
        List<Column> columns = new ArrayList<>();
        Connection connection = getConnection();
        try {
            Statement stmt = connection.createStatement();
            //获取结果集
            ResultSet rs = stmt.executeQuery("SHOW FULL  COLUMNS from  " + table_name);
            while (rs != null && rs.next()) {
                Column column = new Column();
                //获取字段名称
                String field = rs.getString("Field");
                column.setColumnName(field);
                //获取字段描述
                String comment = rs.getString("Comment");
                column.setComment(comment);
                String[] split = field.split("_");
                String upperCaseFieldName = split[0].substring(0, 1).toUpperCase() + split[0].substring(1);
                String fieldName = split[0];
                for (int i = 1; i < split.length; i++) {
                    upperCaseFieldName += split[i].substring(0, 1).toUpperCase() + split[i].substring(1);
                    fieldName += split[i].substring(0, 1).toUpperCase() + split[i].substring(1);
                }
                column.setFieldName(fieldName);
                column.setUpperCasecolumnName(upperCaseFieldName);
                //获取列的数据类型
                String typeName = rs.getString("Type");
                column.setJavaType(sqlType2JavaType(typeName));
                columns.add(column);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return columns;
    }

    /**
     * 功能：获得列的数据类型 ,转化为java中的类型
     *
     * @param sqlType
     * @return
     */
    private String sqlType2JavaType(String sqlType) {
        //将 type(length) 转换为type
        if (sqlType.indexOf("(") != -1) {
            sqlType = sqlType.substring(0, sqlType.indexOf("("));
        }
        if (sqlType.equalsIgnoreCase("bigint") || sqlType.equalsIgnoreCase("number")) {
            return "Long";
        } else if (sqlType.equalsIgnoreCase("char") || sqlType.equalsIgnoreCase("nvarchar2")
                || sqlType.equalsIgnoreCase("varchar2") || sqlType.equalsIgnoreCase("varchar") || sqlType.equalsIgnoreCase("text")) {
            return "String";
        } else if (sqlType.equalsIgnoreCase("date") || sqlType.equalsIgnoreCase("timestamp")
                || sqlType.equalsIgnoreCase("timestamp with local time zone")
                || sqlType.equalsIgnoreCase("timestamp with time zone") || sqlType.equalsIgnoreCase("datetime")) {
            return "Date";
        } else if (sqlType.equalsIgnoreCase("int")) {
            return "Integer";
        }else if (sqlType.equalsIgnoreCase("decimal")) {
            return "BigDecimal";
        }
        return "String";
    }

    /**
     * 获取表的描述
     *
     * @param tableName
     * @return
     */
    public String getTableComment(String tableName) {
        //获取表的注释
        String comment = "";
        try {
            Class.forName(DRIVERCLASS);
            Connection connection = DriverManager.getConnection(JDBCURL, USERNAME, PASSWORD);
            Statement stmt = connection.createStatement();
            ResultSet resultSet = stmt.executeQuery(" SHOW CREATE TABLE " + tableName);
            while (resultSet != null && resultSet.next()) {
                comment = parse(resultSet.getString(2));
            }
            resultSet.close();
            stmt.close();
            connection.close();
            System.out.println(comment);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return comment;
    }

    /**
     * 解析获取表的描述
     *
     * @param all
     * @return
     */
    public String parse(String all) {
        String comment = null;
        int index = all.indexOf("COMMENT='");
        if (index < 0) {
            return "";
        }
        comment = all.substring(index + 9);
        comment = comment.substring(0, comment.length() - 1);
        return comment;
    }
}
